package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.SelectApprovalDao;
import com.mp.approvalreq.entity.*;
import com.mp.approvalreq.entity.dto.*;
import com.mp.approvalreq.util.DateUtils;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class SelectApprovalDaoImpl implements SelectApprovalDao {

    private static final Logger logger = LoggerFactory.getLogger(SelectApprovalDaoImpl.class);

    private Connection conn;

    public SelectApprovalDaoImpl(Connection conn) {
        this.conn = conn;
    }

    @Override
    public RequestDTO getRequestDetail(Integer companyId, Integer requestId) throws SQLException {
        RequestDTO requestDTO = new RequestDTO();

        String sql = " select Fgeneration from request_flow where Fcompany_id = ? and Frequest_id = ? ";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, requestId);
            logger.info(SQLLogger.logSQL(sql, companyId, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    requestDTO.setGeneration(rs.getInt(1));
                    requestDTO.setCompanyId(companyId);
                    requestDTO.setRequestDkey(requestId);
                }
            }
        }

        return requestDTO;
    }

    @Override
    public RequestInfoDTO getRequestInfoDTO(Integer requestId, Integer companyId) throws SQLException {
        RequestInfoDTO requestInfoDTO = new RequestInfoDTO();

        String sql = " select Frequest_id, Fcompany_id, Frequest_step, Frequest_name, Frequest_template_type, " +
                " Frequest_templet, Fversion, Ffinished, Fall_judger, Fall_judger_ciid, Fapplicant, Fapplicant_ciid, " +
                " Fcc, Fwho_the_next, Fwho_the_next_ciid, Fwho_confirm, Fwho_confirm_ciid, Fchecked_time, Fcreate_time, " +
                " Frequest_content_last, Frequest_content_last_total_second, Fnatural_content_time_last, " +
                " Fcall_back_time, Ffinally_cc_time, Fmanual_ending_time, Fouter_position, Frequest_templet   " +
                " from approval.request_flow where Frequest_id = ? and Fcompany_id = ? ";

        SimpleDateFormat sdf = DateUtils.getSimpleDateFormat(DateUtils.YMDHMS);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    requestInfoDTO.setRequestId(rs.getInt(1));
                    requestInfoDTO.setCompanyId(rs.getInt(2));
                    requestInfoDTO.setRequestStep(rs.getInt(3));
                    requestInfoDTO.setRequestName(rs.getString(4));
                    requestInfoDTO.setDefaultType(rs.getInt(5));
                    requestInfoDTO.setTemplateId(rs.getInt(6));
                    requestInfoDTO.setVersion(rs.getInt(7));
                    requestInfoDTO.setFinished(rs.getInt(8));
                    requestInfoDTO.setAllJudger(rs.getString(9));
                    requestInfoDTO.setAllJudgerCiid(rs.getString(10));
                    requestInfoDTO.setApplicant(rs.getInt(11));
                    requestInfoDTO.setApplicantCiid(rs.getInt(12));
                    requestInfoDTO.setCc(rs.getString(13));
                    requestInfoDTO.setNextJudger(rs.getString(14));
                    requestInfoDTO.setNextJudgerCiid(rs.getString(15));
                    requestInfoDTO.setConfirmer(rs.getString(16));
                    requestInfoDTO.setConfirmerCiid(rs.getString(17));
                    requestInfoDTO.setCheckedTime(rs.getString(18));
                    requestInfoDTO.setRequestTime(sdf.format(rs.getTimestamp(19)));
                    requestInfoDTO.setRequestContentLastStr(rs.getString(20));
                    requestInfoDTO.setRequestContentLastTotalSecond(rs.getString(21));
                    requestInfoDTO.setNaturalContentTimeLast(rs.getString(22));
                    requestInfoDTO.setCallBackTime(rs.getString(23));
                    requestInfoDTO.setFinallyCcTime(rs.getString(24));
                    requestInfoDTO.setManualEndingTimeStr(rs.getString(25));
                    requestInfoDTO.setOutPosition(rs.getString(26));
                    requestInfoDTO.setTemplateId(rs.getInt(27));
                }
            }
        }
        return requestInfoDTO;
    }

    @Override
    public RequestInfoDTO getRequestInfoDTO(Integer requestId) throws SQLException {
        RequestInfoDTO requestInfoDTO = new RequestInfoDTO();

        String sql = " select Frequest_id, Fcompany_id, Frequest_step, Frequest_name, Frequest_template_type, " +
                " Frequest_templet, Fversion, Ffinished, Fall_judger, Fall_judger_ciid, Fapplicant, Fapplicant_ciid, " +
                " Fcc, Fwho_the_next, Fwho_the_next_ciid, Fwho_confirm, Fwho_confirm_ciid, Fchecked_time, Fcreate_time, " +
                " Frequest_content_last, Frequest_content_last_total_second, Fnatural_content_time_last, " +
                " Fcall_back_time, Ffinally_cc_time, Fmanual_ending_time, Fouter_position " +
                " from approval.request_flow where Frequest_id = ? ";

        SimpleDateFormat sdf = DateUtils.getSimpleDateFormat(DateUtils.YMDHMS);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    requestInfoDTO.setRequestId(rs.getInt(1));
                    requestInfoDTO.setCompanyId(rs.getInt(2));
                    requestInfoDTO.setRequestStep(rs.getInt(3));
                    requestInfoDTO.setRequestName(rs.getString(4));
                    requestInfoDTO.setDefaultType(rs.getInt(5));
                    requestInfoDTO.setTemplateId(rs.getInt(6));
                    requestInfoDTO.setVersion(rs.getInt(7));
                    requestInfoDTO.setFinished(rs.getInt(8));
                    requestInfoDTO.setAllJudger(rs.getString(9));
                    requestInfoDTO.setAllJudgerCiid(rs.getString(10));
                    requestInfoDTO.setApplicant(rs.getInt(11));
                    requestInfoDTO.setApplicantCiid(rs.getInt(12));
                    requestInfoDTO.setCc(rs.getString(13));
                    requestInfoDTO.setNextJudger(rs.getString(14));
                    requestInfoDTO.setNextJudgerCiid(rs.getString(15));
                    requestInfoDTO.setConfirmer(rs.getString(16));
                    requestInfoDTO.setConfirmerCiid(rs.getString(17));
                    requestInfoDTO.setCheckedTime(rs.getString(18));
                    requestInfoDTO.setRequestTime(sdf.format(rs.getTimestamp(19)));
                    requestInfoDTO.setRequestContentLastStr(rs.getString(20));
                    requestInfoDTO.setRequestContentLastTotalSecond(rs.getString(21));
                    requestInfoDTO.setNaturalContentTimeLast(rs.getString(22));
                    requestInfoDTO.setCallBackTime(rs.getString(23));
                    requestInfoDTO.setFinallyCcTime(rs.getString(24));
                    requestInfoDTO.setManualEndingTimeStr(rs.getString(25));
                    requestInfoDTO.setOutPosition(rs.getString(26));
                }
            }
        }
        return requestInfoDTO;
    }

    @Override
    public List<RequestInfoDTO> getApprovalsCostByMonth(Integer companyId, String month, List<Integer> templateIds) throws SQLException {
        List<RequestInfoDTO> requestInfoDTOS = new LinkedList<>();

        //空则返回
        if (Utils.isEmpty(templateIds)) {
            return requestInfoDTOS;
        }

        StringBuilder sql = new StringBuilder(" select Frequest_id, Ftotal_money, Frequest_template_type, Frequest_templet " +
                " from approval.request_flow " +
                " where Fcompany_id = ? and Ffinished in(1,2) and date_format(Ffinally_confirmed_time, '%Y-%m') = ? " +
                " and Frequest_templet in(");
        for (int i = 0; i < templateIds.size(); i++) {
            sql.append(templateIds.get(i));
            if (templateIds.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(")");

        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            ps.setInt(1, companyId);
            ps.setString(2, month);
            logger.info(SQLLogger.logSQL(sql.toString(), companyId, month));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    RequestInfoDTO requestInfoDTO = new RequestInfoDTO();
                    requestInfoDTO.setRequestId(rs.getInt(1));
                    requestInfoDTO.setTotalMoney(rs.getString(2));
                    requestInfoDTO.setDefaultType(rs.getInt(3));
                    requestInfoDTO.setTemplateId(rs.getInt(4));
                    requestInfoDTOS.add(requestInfoDTO);
                }
            }
        }

        return requestInfoDTOS;
    }

    @Override
    public List<GeneralRequestDTO> getProjectGeneralRequestFlows(Integer companyId, List<Integer> projectIds) throws SQLException {
        List<GeneralRequestDTO> generalRequestDTOS = new ArrayList<>();

        //空则返回
        if (Utils.isEmpty(projectIds)) {
            return generalRequestDTOS;
        }


        StringBuilder sql = new StringBuilder(" select Pid, Pproject_id, Prequest_flow " +
                " from approval.pt_mp_general_request_flow " +
                " where Pcompany_id = ? and Fstate = 0 and Pproject_id in(");
        for (int i = 0; i < projectIds.size(); i++) {
            sql.append(projectIds.get(i));
            if (projectIds.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(")");

        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            ps.setInt(1, companyId);

            logger.info(SQLLogger.logSQL(sql.toString(), companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    GeneralRequestDTO generalRequestDTO = new GeneralRequestDTO();
                    generalRequestDTO.setId(rs.getInt(1));
                    generalRequestDTO.setProject(rs.getInt(2));
                    generalRequestDTO.setAllJudger(rs.getString(3));

                    generalRequestDTOS.add(generalRequestDTO);
                }
            }
        }
        return generalRequestDTOS;
    }

    @Override
    public Map<Integer, List<GeneralConditionFlowDTO>> getGeneralConditionFlowDTOs(Integer companyId, List<Integer> generalConditionFlowIdList) throws SQLException {
        Map<Integer, List<GeneralConditionFlowDTO>> map = new HashMap<>();
        List<GeneralConditionFlowDTO> generalConditionFlowDTOs = new ArrayList<>();

        //空则返回
        if (Utils.isEmpty(generalConditionFlowIdList)) {
            return map;
        }


        StringBuilder sql = new StringBuilder(" select Fid, Fgeneral_flow_id, Fcondition_type, Fcondition_calculation_type, Fsmaller_factor, " +
                " Fgreater_factor, Frange, Frequest_flow, Fis_allowed_to_edit_judger " +
                " from  pt_mp_general_request_condition_flow_desc " +
                " where Fcompany_id = ? and Fgeneral_flow_id in(");
        for (int i = 0; i < generalConditionFlowIdList.size(); i++) {
            sql.append(generalConditionFlowIdList.get(i));
            if (generalConditionFlowIdList.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(")");

        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            ps.setInt(1, companyId);

            logger.info(SQLLogger.logSQL(sql.toString(), companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    GeneralConditionFlowDTO generalConditionFlowDTO = new GeneralConditionFlowDTO();
                    generalConditionFlowDTO.setId(rs.getInt(1));
                    generalConditionFlowDTO.setGeneralFlowId(rs.getInt(2));
                    generalConditionFlowDTO.setConditionType(rs.getInt(3));
                    generalConditionFlowDTO.setConditionCalculationType(rs.getInt(4));
                    generalConditionFlowDTO.setSmallerFactor(rs.getBigDecimal(5));
                    generalConditionFlowDTO.setGreaterFactor(rs.getBigDecimal(6));
                    generalConditionFlowDTO.setRange(rs.getString(7));
                    generalConditionFlowDTO.setRequestFlow(rs.getString(8));
                    generalConditionFlowDTO.setIsAllowedToEditJudger(rs.getInt(9));

                    generalConditionFlowDTOs.add(generalConditionFlowDTO);
                }
            }
        }

        for (GeneralConditionFlowDTO generalConditionFlowDTO : generalConditionFlowDTOs) {
            if (map.containsKey(generalConditionFlowDTO.getGeneralFlowId())) {
                List<GeneralConditionFlowDTO> generalConditionFlowDTOList = map.get(generalConditionFlowDTO.getGeneralFlowId());
                generalConditionFlowDTOList.add(generalConditionFlowDTO);
            } else {
                List<GeneralConditionFlowDTO> generalConditionFlowDTOList = new ArrayList<>();
                generalConditionFlowDTOList.add(generalConditionFlowDTO);
                map.put(generalConditionFlowDTO.getGeneralFlowId(), generalConditionFlowDTOList);
            }
        }

        return map;
    }

    @Override
    public List<RequestInfoDTO> getNeedApprovalList(Integer companyId, Integer companyInfoId) throws SQLException {

        List<RequestInfoDTO> requestInfoDTOs = new LinkedList<>();

        String sql = " select Frequest_id, Fcreate_time from approval.request_flow" +
                " where Fcompany_id = ? and Ffinished = 0 and Fwho_the_next_ciid = ? ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, companyInfoId.toString());

            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    RequestInfoDTO requestInfoDTO = new RequestInfoDTO();
                    requestInfoDTO.setRequestId(rs.getInt(1));
                    requestInfoDTO.setRequestTime(rs.getString(2));

                    requestInfoDTOs.add(requestInfoDTO);
                }
            }
        }

        return requestInfoDTOs;
    }

    @Override
    public List<RequestInfoDTO> getOverWorkTimeRequests(Integer companyInfoId) throws SQLException {
        List<RequestInfoDTO> requestInfoDTOs = new LinkedList<>();

        String sql = " select Frequest_id, Fouter_position, Frequest_templet, Fcompany_id, " +
                " Fcreate_time, Frequest_name, Fapplicant, Ffinished, Frequest_ancestor_id, Fversion, " +
                " Frequest_template_type, Fapplicant, Frela_people  " +
                " from approval.request_flow " +
                " where Fapplicant_ciid = ? and Frequest_template_type = 9 " +
                " and Ffinished in(0,1,2)  ";

        SimpleDateFormat sdf = DateUtils.getSimpleDateFormat(DateUtils.YMDHMS);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    RequestInfoDTO requestInfoDTO = new RequestInfoDTO();

                    requestInfoDTO.setRequestId(rs.getInt(1));
                    requestInfoDTO.setOutPosition(rs.getString(2));
                    requestInfoDTO.setTemplateId(rs.getInt(3));
                    requestInfoDTO.setCompanyId(rs.getInt(4));
                    requestInfoDTO.setRequestTime(sdf.format(rs.getTimestamp(5).getTime()));
                    requestInfoDTO.setRequestName(rs.getString(6));
                    requestInfoDTO.setApplicant(rs.getInt(7));
                    requestInfoDTO.setFinished(rs.getInt(8));
                    requestInfoDTO.setRequestAncestorId(rs.getInt(9));
                    requestInfoDTO.setVersion(rs.getInt(10));
                    requestInfoDTO.setDefaultType(rs.getInt(11));
                    requestInfoDTO.setApplicant(rs.getInt(12));
                    requestInfoDTO.setRelaPeopleContactIds(rs.getString(13));

                    requestInfoDTOs.add(requestInfoDTO);
                }
            }
        }

        return requestInfoDTOs;
    }

    @Override
    public List<DaysOffTimeInfo> selectDaysOffRequestList(Integer companyInfoId) throws SQLException {
        List<DaysOffTimeInfo> list = new ArrayList<>();
        SimpleDateFormat sdf = DateUtils.getSimpleDateFormat(DateUtils.YMDHMS);

        String sql = " select Frequest_id, Fcreate_time from approval.request_flow where " +
                " Fapplicant_ciid = ? and Frequest_template_type = 12 and Ffinished in(0,1,2) ";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    DaysOffTimeInfo daysOffTimeInfo = new DaysOffTimeInfo();
                    daysOffTimeInfo.setRequestId(rs.getInt(1));
                    daysOffTimeInfo.setCreateTime(sdf.format(rs.getTimestamp(2).getTime()));

                    list.add(daysOffTimeInfo);
                }
            }
        }
        return list;
    }

    @Override
    public List<RequestInfoDTO> selectDaysOffRequests(Integer companyInfoId) throws SQLException {
        List<RequestInfoDTO> list = new ArrayList<>();

        String sql = " select Frequest_id, Frequest_templet, Fcompany_id, Fcreate_time, Frequest_name," +
                " Fapplicant, Ffinished, Frequest_ancestor_id, Fversion, Frequest_template_type " +
                " from approval.request_flow where " +
                " Fapplicant_ciid = ? and Frequest_template_type = 12 and Ffinished in(0,1,2) ";

        SimpleDateFormat sdf = DateUtils.getSimpleDateFormat(DateUtils.YMDHMS);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    RequestInfoDTO requestInfoDTO = new RequestInfoDTO();
                    requestInfoDTO.setRequestId(rs.getInt(1));
                    requestInfoDTO.setTemplateId(rs.getInt(2));
                    requestInfoDTO.setCompanyId(rs.getInt(3));
                    requestInfoDTO.setRequestTime(sdf.format(rs.getTimestamp(4).getTime()));
                    requestInfoDTO.setRequestName(rs.getString(5));
                    requestInfoDTO.setApplicant(rs.getInt(6));
                    requestInfoDTO.setFinished(rs.getInt(7));
                    requestInfoDTO.setRequestAncestorId(rs.getInt(8));
                    requestInfoDTO.setVersion(rs.getInt(9));
                    requestInfoDTO.setDefaultType(rs.getInt(10));

                    list.add(requestInfoDTO);
                }
            }
        }

        return list;
    }

    @Override
    public Integer selectRequestNewestStatus(Integer id) throws SQLException {
        String sql = "select Ffinished from request_flow where (Frequest_id = ? or Frequest_ancestor_id =?) order by Frequest_id desc limit 1";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ps.setInt(2, id);
            logger.info(SQLLogger.logSQL(sql, id, id));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        }
        return -99;
    }

    @Override
    public List<RequestInfoDTO> selectLeaveTimeRequestsByGeneration(Integer companyInfoId, Integer generation) throws SQLException {
        List<RequestInfoDTO> requestInfoDTOs = new LinkedList<>();

        String sql = " select Frequest_id, Fouter_position, Frequest_templet, Fcompany_id, " +
                " Fcreate_time, Frequest_name, Fapplicant, Ffinished, Frequest_ancestor_id, " +
                " Fversion, Fnatural_content_time_last, Frequest_template_type, Frequest_content_last_total_second " +
                " from approval.request_flow " +
                " where Fapplicant_ciid = ? and Fgeneration >= ? " +
                " and Frequest_template_type = 2 and Ffinished in(1,2) ";

        SimpleDateFormat sdf = DateUtils.getSimpleDateFormat(DateUtils.YMDHMS);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            ps.setInt(2, generation);
            logger.info(SQLLogger.logSQL(sql, companyInfoId, generation));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    RequestInfoDTO requestInfoDTO = new RequestInfoDTO();
                    requestInfoDTO.setRequestId(rs.getInt(1));
                    requestInfoDTO.setOutPosition(rs.getString(2));
                    requestInfoDTO.setTemplateId(rs.getInt(3));
                    requestInfoDTO.setCompanyId(rs.getInt(4));
                    requestInfoDTO.setRequestTime(sdf.format(rs.getTimestamp(5).getTime()));
                    requestInfoDTO.setRequestName(rs.getString(6));
                    requestInfoDTO.setApplicant(rs.getInt(7));
                    requestInfoDTO.setFinished(rs.getInt(8));
                    requestInfoDTO.setRequestAncestorId(rs.getInt(9));
                    requestInfoDTO.setVersion(rs.getInt(10));
                    requestInfoDTO.setNaturalContentTimeLast(rs.getString(11));
                    requestInfoDTO.setDefaultType(rs.getInt(12));
                    requestInfoDTO.setRequestContentLastTotalSecond(rs.getString(13));

                    requestInfoDTOs.add(requestInfoDTO);
                }
            }
        }

        return requestInfoDTOs;
    }

    @Override
    public List<RequestTime> selectAllRequestTime(Integer requestId) throws SQLException {
        List<RequestTime> list = new ArrayList<>();

        String sql = " select id, request_id, days_off_time, date from request_time where request_id = ? ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    RequestTime requestTime = new RequestTime();
                    requestTime.setId(rs.getInt(1));
                    requestTime.setRequestId(rs.getInt(2));
                    requestTime.setDaysOffTimes(rs.getString(3));
                    requestTime.setDate(rs.getString(4));
                    list.add(requestTime);
                }
            }
        }
        return list;
    }

    @Override
    public List<RequestTime> selectRequestTimeList(List<Integer> requestIds) throws SQLException {
        List<RequestTime> list = new ArrayList<>();

        //空则返回
        if (Utils.isEmpty(requestIds)) {
            return list;
        }

        StringBuilder sql = new StringBuilder(" select request_id, sum(days_off_time) from request_time where request_id in(");
        for (int i = 0; i < requestIds.size(); i++) {
            sql.append(requestIds.get(i));
            if (requestIds.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(") group by request_id");

        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            logger.info(sql.toString());

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    RequestTime requestTime = new RequestTime();
                    requestTime.setRequestId(rs.getInt(1));
                    requestTime.setDaysOffTimes(rs.getString(2));

                    list.add(requestTime);
                }
            }
        }

        return list;
    }

    @Override
    public List<ApprovalCost> getCostApprovals(Integer companyId, String requestIds) throws SQLException {
        List<ApprovalCost> list = new ArrayList<>();

        //空则返回
        if (StringUtils.isBlank(requestIds)) {
            return list;
        }
        String sql = " select f.Frequest_id, f.Frequest_template_type, f.Ftotal_money, " +
                " t.Frelate_analysis_num, t.Ftemplate_name, t.Ftemplate_id, t.Fversion, f.Fapplicant  " +
                " from request_flow f inner join request_template t on f.Frequest_templet = t.Ftemplate_id " +
                " where f.Fcompany_id = ? and t.Fis_related_to_cost_analysis = 1 " +
                " and f.Ffinished in(1,2) and f.Frequest_id in(" + requestIds + ")";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ApprovalCost approvalCost = new ApprovalCost();
                    approvalCost.setRequestId(rs.getInt(1));
                    approvalCost.setTemplateType(rs.getInt(2));
                    approvalCost.setTotalMoney(rs.getString(3));
                    approvalCost.setAnalysisNum(rs.getString(4));
                    approvalCost.setRequestName(rs.getString(5));
                    approvalCost.setTemplateId(rs.getInt(6));
                    approvalCost.setVersion(rs.getInt(7));
                    approvalCost.setApplicant(rs.getInt(8));

                    list.add(approvalCost);
                }
            }
        }

        return list;
    }

    @Override
    public List<Request> selectRequestListByTemplateType(Integer companyInfoId, Integer templateType, String finished) throws SQLException {
        List<Request> result = new ArrayList<>();
        SimpleDateFormat sdf = DateUtils.getSimpleDateFormat(DateUtils.YMDHMS);

        //空则返回
        if (StringUtils.isBlank(finished)) {
            return result;
        }

        String sql = " select Frequest_id, Frequest_templet, Frequest_template_type, Fcompany_id, " +
                " Fapplicant_ciid, Fcreate_time, Ffinished from approval.request_flow " +
                " where Fapplicant_ciid = ? and Frequest_template_type = ? " +
                " and Ffinished in(" + finished + ")";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            ps.setInt(2, templateType);
            logger.info(SQLLogger.logSQL(sql, companyInfoId, templateType));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = new Request();
                    request.setRequestDkey(rs.getInt(1));
                    request.setTemplateId(rs.getInt(2));
                    request.setDefaultType(rs.getInt(3));
                    request.setCompanyId(rs.getInt(4));
                    request.setApplicantCiid(rs.getInt(5));
                    request.setRequestTime(sdf.format(rs.getTimestamp(6).getTime()));
                    request.setCreateTime(sdf.format(rs.getTimestamp(6).getTime()));
                    request.setFinished(rs.getInt(7));
                    result.add(request);
                }
            }
        }

        return result;
    }

}
